package assistant.dao.daoImpl;

import achievement.domain.SzlBook;
import assistant.dao.CourseDao;
import assistant.domain.Course;
import assistant.domain.Evaluate;
import cn.hutool.core.util.StrUtil;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @Date 2022/12/17 17:53
 * @Author lihu
 */
public class CourseDaoImpl implements CourseDao {
    private Connection con;
    private PreparedStatement psmt;
    private ResultSet rs;

    @Override
    public void insert(Course course) {
        String sql = "insert into lh_course\n" +
                "        (teacher_id,`name`,course_type, student_type, student_number, `hour`, term, `year`) \n" +
                "values(?,?,?,?,?,?,?,?)";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1, course.getTeacherId());
            psmt.setString(2, course.getName());
            psmt.setInt(3, course.getCourseType());
            psmt.setString(4, course.getStudentType());
            psmt.setInt(5, course.getStudentNumber());
            psmt.setInt(6, course.getHour());
            psmt.setString(7, course.getTerm());
            psmt.setString(8, course.getYear());
            psmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(con, psmt);
        }
    }

    @Override
    public void updateById(Course course) {
        /*(teracher_id, ,`name`,course_type, student_type, student_number, term, `year`) \n" +*/
        String sql = "update lh_course set";
        List<Object> param = new ArrayList<>();
        if (course == null) {
            return;
        } else {
            if (course.getTeacherId() != null) {
                sql += " teacher_id = ?,";
                param.add(course.getTeacherId());
            }
            if (course.getName() != null) {
                sql += " `name`= ?,";
                param.add(course.getName());
            }
            if (course.getCourseType() != null) {
                sql += " course_type = ?,";
                param.add(course.getCourseType());
            }
            if (course.getStudentType() != null) {
                sql += " `student_type` = ?,";
                param.add(course.getStudentType());
            }
            if (course.getStudentNumber() != null) {
                sql += " student_number = ?,";
                param.add(course.getStudentNumber());
            }
            if (course.getHour() != null) {
                sql += "`hour` = ?,";
                param.add(course.getHour());
            }
            if (course.getTerm() != null) {
                sql += " term = ?,";
                param.add(course.getTerm());
            }
            if (course.getYear() != null) {
                sql += "  `year` = ?,";
                param.add(course.getYear());
            }
        }
        sql = StrUtil.strip(sql, null, ",");
        sql += " where id = ?";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for (int i = 0; i < param.size(); i++) {
                psmt.setObject(i + 1, param.get(i));
            }
            psmt.setLong(param.size() + 1, course.getId());
            psmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(con, psmt);
        }
    }

    @Override
    public void deleteById(Long id) {
        String sql = "delete from lh_course where id = ?";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1, id);
            psmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(con, psmt);
        }
    }

    @Override
    public Course selectById(Long id) {
        /*(teracher_id, ,`name`,course_type, student_type, student_number, term, `year`) \n" +*/

        String sql = "select * from lh_course where id = ?";
        Course course = new Course();
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1, id);
            rs = psmt.executeQuery();
            while (rs.next()) {
                course.setId(rs.getLong(1));
                course.setTeacherId(rs.getLong(2));
                course.setName(rs.getString(3));
                course.setCourseType(rs.getInt(4));
                course.setStudentType(rs.getString(5));
                course.setStudentNumber(rs.getInt(6));
                course.setHour(rs.getInt(7));
                course.setTerm(rs.getString(8));
                course.setYear(rs.getString(9));

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(con, psmt, rs);
        }
        return course;
    }

    @Override
    public List<Course> selectList(Course course) {
        String sql = "select * from lh_course where 1=1";
        List<Course> list = new ArrayList<>();
        List<Object> param = new ArrayList<>();
        if (course == null) {
            sql = "select * from lh_course";
        } else {
            if (course.getTeacherId() != null) {
                sql += " and  teacher_id = ?";
                param.add(course.getTeacherId());
            }
            if (course.getName() != null) {
                sql += " and `name`= ?";
                param.add(course.getName());
            }
            if (course.getCourseType() != null) {
                sql += " and course_type = ?";
                param.add(course.getCourseType());
            }
            if (course.getStudentType() != null) {
                sql += " and `student_type` = ?";
                param.add(course.getStudentType());
            }
            if (course.getStudentNumber() != null) {
                sql += " and student_number = ?";
                param.add(course.getStudentNumber());
            }
            if (course.getHour() != null) {
                sql += " and `hour` = ?";
                param.add(course.getHour());
            }
            if (course.getTerm() != null) {
                sql += " and term = ?";
                param.add(course.getTerm());
            }
            if (course.getYear() != null) {
                sql += " and  `year` = ?";
                param.add(course.getYear());
            }
        }
//        sql += "order by student_number and `hour` desc";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for (int i = 0; i < param.size(); i++) {
                psmt.setObject(i + 1, param.get(i));
            }
            rs = psmt.executeQuery();
            while (rs.next()) {
                Course course1 = new Course();
                course.setId(rs.getLong(1));
                course.setTeacherId(rs.getLong(2));
                course.setName(rs.getString(3));
                course.setCourseType(rs.getInt(4));
                course.setStudentType(rs.getString(5));
                course.setStudentNumber(rs.getInt(6));
                course.setHour(rs.getInt(7));
                course.setTerm(rs.getString(8));
                course.setYear(rs.getString(9));
                list.add(course1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(con, psmt, rs);
        }
        return list;
    }
}
